package com.bojx.report.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import com.bojx.report.dao.BasicDataDao;
import com.bojx.report.dao.QuarterReportDao;
import com.bojx.report.pojo.Constant;
import com.jfinal.kit.PathKit;
import com.jfinal.kit.PropKit;
import com.jfinal.plugin.activerecord.Record;

public class QuarterExcelService {
	private static Logger LOG = Logger.getLogger(ExcelService.class);
	private static BasicDataDao dao = new BasicDataDao();
	private static QuarterReportDao quarterReportDao = new QuarterReportDao();

	public String generateQuarterReport(int year, int quarter) {
		FileInputStream fis;
		try {
			PropKit.use("appconfig.properties", "UTF-8");
			String reportFolder = PropKit.get("reportFolder");
			fis = new FileInputStream(PathKit.getWebRootPath() + "/WEB-INF/example/季报.xls");
			HSSFWorkbook wb = new HSSFWorkbook(fis);
			produceBankTable(wb, year, quarter);// 生成分行别统计表
			produceAreaTable(wb, year, quarter);// 生成分辖区统计表
			String fileName = "季报" + year + "-第" + quarter + "季度.xls";
			FileOutputStream fos = new FileOutputStream(new File(reportFolder + "/" + fileName));
			HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
			wb.write(fos);
			fis.close();
			fos.close();
			return fileName;
		} catch (FileNotFoundException e) {
			LOG.error(e.getMessage(), e);
		} catch (IOException e) {
			LOG.error(e.getMessage(), e);
		}
		return null;
	}

	private void produceAreaTable(HSSFWorkbook wb, int year, int quarter) {
		HSSFSheet sheet = wb.getSheetAt(1);
		String[] areas = new String[] { "本级", "嘉善", "桐乡", "平湖", "海宁", "海盐" };
		for (int i = 0; i < areas.length; i++) {
			String area = areas[i];
			Row row = sheet.getRow(i + 8);
			Record branchRecord = quarterReportDao.sumBranchDataByArea(area, year, quarter);

			Integer branchQty = branchRecord.getInt("branch_qty");// 对公网点数量
			Integer edraftBranchQty = branchRecord.getInt("edraft_branch_qty");// 已加入电子商业汇票系统网点数量
			if (branchQty != null) {
				row.getCell(1).setCellValue(branchQty);
			}
			if (edraftBranchQty != null) {
				row.getCell(2).setCellValue(edraftBranchQty);
			}

			// 查询客户情况
			Record clientRecord = quarterReportDao.sumClientDataByArea(area, year, quarter);

			Integer euserQty = clientRecord.getInt("euser_qty");// 全部企业类客户数量
			if (euserQty != null) {
				row.getCell(3).setCellValue(euserQty);
			}
			Integer ebankEuserQty = clientRecord.getInt("ebank_euser_qty");// 开通网银的企业类客户数量
			if (ebankEuserQty != null) {
				row.getCell(4).setCellValue(ebankEuserQty);
			}

			Integer draftEuserQty = clientRecord.getInt("draft_euser_qty");// 已使用商业汇票客户数量
			if (draftEuserQty != null) {
				row.getCell(5).setCellValue(draftEuserQty);
			}
			Integer edraftEuserQty = clientRecord.getInt("edraft_euser_qty");// 已开通电子商业汇票业务客户数量

			if (edraftEuserQty != null) {
				row.getCell(6).setCellValue(edraftEuserQty);
			}

			// 查询业务量
			// 电子银行承兑汇票承兑
			setCellValueByArea(row, area, year, quarter, Constant.baEdraftAccept,
					new int[] { 7, 8, 9, 10, 11, 12, 13, 14 });
			// 电子银行承兑汇票贴现
			setCellValueByArea(row, area, year, quarter, Constant.baEdraftDiscount,
					new int[] { 15, 16, 17, 18, 19, 20, 21, 22 });
			// 纸质银行承兑汇票承兑
			setCellValueByArea(row, area, year, quarter, Constant.baDraftAccept,
					new int[] { 23, 24, 25, 26, 27, 28, 29, 30 });
			// 纸质银行承兑汇票贴现
			setCellValueByArea(row, area, year, quarter, Constant.baDraftDiscount,
					new int[] { 31, 32, 33, 34, 35, 36, 37, 38 });
			// 电子商业承兑汇票贴现
			setCellValueByArea(row, area, year, quarter, Constant.caEdraftDiscount,
					new int[] { 39, 40, 41, 42, 43, 44, 45, 46 });
			// 纸质商业承兑汇票贴现
			setCellValueByArea(row, area, year, quarter, Constant.caDraftDiscount,
					new int[] { 47, 48, 49, 50, 51, 52, 53, 54 });
			// 商业汇票再贴现
			setCellValueByArea(row, area, year, quarter, Constant.draftRediscount,
					new int[] { 55, 56, 57, 58, 59, 60, 61, 62 });
		}
	}

	private void produceBankTable(HSSFWorkbook wb, int year, int quarter) {
		HSSFSheet sheet = wb.getSheetAt(0);
		List<Record> banks = dao.findBanks();

		for (Record bankRecord : banks) {
			String bank = bankRecord.getStr("organization");
			int rowNum = bankRecord.getInt("row_num_sum");
			Row row = sheet.getRow(rowNum - 1);
			// 查询银行网点信息
			Record branchRecord = quarterReportDao.sumBranchDataByBank(bank, year, quarter);

			Integer branchQty = branchRecord.getInt("branch_qty");// 对公网点数量
			Integer edraftBranchQty = branchRecord.getInt("edraft_branch_qty");// 已加入电子商业汇票系统网点数量
			if (branchQty != null) {
				row.getCell(1).setCellValue(branchQty);
			}
			if (edraftBranchQty != null) {
				row.getCell(2).setCellValue(edraftBranchQty);
			}

			// 查询客户情况
			Record clientRecord = quarterReportDao.sumClientDataByBank(bank, year, quarter);

			Integer euserQty = clientRecord.getInt("euser_qty");// 全部企业类客户数量
			if (euserQty != null) {
				row.getCell(3).setCellValue(euserQty);
			}
			Integer ebankEuserQty = clientRecord.getInt("ebank_euser_qty");// 开通网银的企业类客户数量
			if (ebankEuserQty != null) {
				row.getCell(4).setCellValue(ebankEuserQty);
			}

			Integer draftEuserQty = clientRecord.getInt("draft_euser_qty");// 已使用商业汇票客户数量
			if (draftEuserQty != null) {
				row.getCell(5).setCellValue(draftEuserQty);
			}
			Integer edraftEuserQty = clientRecord.getInt("edraft_euser_qty");// 已开通电子商业汇票业务客户数量

			if (edraftEuserQty != null) {
				row.getCell(6).setCellValue(edraftEuserQty);
			}

			// 查询业务量
			// 电子银行承兑汇票承兑
			setCellValue(row, bank, year, quarter, Constant.baEdraftAccept
					, new int[] { 7, 8, 9, 10, 11, 12, 13, 14 });
			// 电子银行承兑汇票贴现
			setCellValue(row, bank, year, quarter, Constant.baEdraftDiscount,
					new int[] { 15, 16, 17, 18, 19, 20, 21, 22 });
			// 纸质银行承兑汇票承兑
			setCellValue(row, bank, year, quarter, Constant.baDraftAccept,
					new int[] { 23, 24, 25, 26, 27, 28, 29, 30 });
			// 纸质银行承兑汇票贴现
			setCellValue(row, bank, year, quarter, Constant.baDraftDiscount,
					new int[] { 31, 32, 33, 34, 35, 36, 37, 38 });
			// 电子商业承兑汇票贴现
			setCellValue(row, bank, year, quarter, Constant.caEdraftDiscount,
					new int[] { 39, 40, 41, 42, 43, 44, 45, 46 });
			// 纸质商业承兑汇票贴现
			setCellValue(row, bank, year, quarter, Constant.caDraftDiscount,
					new int[] { 47, 48, 49, 50, 51, 52, 53, 54 });
			// 商业汇票再贴现
			setCellValue(row, bank, year, quarter, Constant.draftRediscount,
					new int[] { 55, 56, 57, 58, 59, 60, 61, 62 });
		}
	}

	

	private void setCellValue(Row row, String bank, int year, int quarter, String billType, int[] cellNums) {
		// 50万以下
		Record less50record = quarterReportDao.sumBusinessDataByBank(bank, billType, Constant.less50, year, quarter);
		Integer less50Qty = less50record.getInt("qty");
		Double less50Amt = less50record.getDouble("amt");
		if (less50Qty != null) {
			row.getCell(cellNums[0]).setCellValue(less50Qty);
		}
		if (less50Amt != null) {
			row.getCell(cellNums[1]).setCellValue(less50Amt);
		}

		// 50-100万
		Record bet50To100Record = quarterReportDao.sumBusinessDataByBank(bank, billType, Constant.bet50To100, year,
				quarter);

		Integer bet50To100Qty = bet50To100Record.getInt("qty");
		Double bet50To100Amt = bet50To100Record.getDouble("amt");
		if (bet50To100Qty != null) {
			row.getCell(cellNums[2]).setCellValue(bet50To100Qty);
		}
		if (bet50To100Amt != null) {
			row.getCell(cellNums[3]).setCellValue(bet50To100Amt);
		}

		// 100-300万
		Record bet100To300Record = quarterReportDao.sumBusinessDataByBank(bank, billType, Constant.bet100To300, year,
				quarter);

		Integer bet100To300Qty = bet100To300Record.getInt("qty");
		Double bet100To300Amt = bet100To300Record.getDouble("amt");
		if (bet100To300Qty != null) {
			row.getCell(cellNums[4]).setCellValue(bet100To300Qty);
		}
		if (bet100To300Amt != null) {
			row.getCell(cellNums[5]).setCellValue(bet100To300Amt);
		}

		// 300万以上
		Record gt300Record = quarterReportDao.sumBusinessDataByBank(bank, billType, Constant.gt300, year, quarter);

		Integer gt300Qty = gt300Record.getInt("qty");
		Double gt300Amt = gt300Record.getDouble("amt");
		if (gt300Qty != null) {
			row.getCell(cellNums[6]).setCellValue(gt300Qty);
		}
		if (gt300Amt != null) {
			row.getCell(cellNums[7]).setCellValue(gt300Amt);
		}

	}

	private void setCellValueByArea(Row row, String area, int year, int quarter, String billType, int[] cellNums) {
		// 50万以下
		Record less50record = quarterReportDao.sumBusinessDataByArea(area
				, billType, Constant.less50, year, quarter);
		Integer less50Qty = less50record.getInt("qty");
		Double less50Amt = less50record.getDouble("amt");
		if (less50Qty != null) {
			row.getCell(cellNums[0]).setCellValue(less50Qty);
		}
		if (less50Amt != null) {
			row.getCell(cellNums[1]).setCellValue(less50Amt);
		}

		// 50-100万
		Record bet50To100Record = quarterReportDao.sumBusinessDataByArea(area
				, billType, Constant.bet50To100, year,
				quarter);

		Integer bet50To100Qty = bet50To100Record.getInt("qty");
		Double bet50To100Amt = bet50To100Record.getDouble("amt");
		if (bet50To100Qty != null) {
			row.getCell(cellNums[2]).setCellValue(bet50To100Qty);
		}
		if (bet50To100Amt != null) {
			row.getCell(cellNums[3]).setCellValue(bet50To100Amt);
		}

		// 100-300万
		Record bet100To300Record = quarterReportDao.sumBusinessDataByArea(area
				, billType, Constant.bet100To300, year,
				quarter);

		Integer bet100To300Qty = bet100To300Record.getInt("qty");
		Double bet100To300Amt = bet100To300Record.getDouble("amt");
		if (bet100To300Qty != null) {
			row.getCell(cellNums[4]).setCellValue(bet100To300Qty);
		}
		if (bet100To300Amt != null) {
			row.getCell(cellNums[5]).setCellValue(bet100To300Amt);
		}

		// 300万以上
		Record gt300Record = quarterReportDao.sumBusinessDataByArea(
				area, billType, Constant.gt300, year, quarter);

		Integer gt300Qty = gt300Record.getInt("qty");
		Double gt300Amt = gt300Record.getDouble("amt");
		if (gt300Qty != null) {
			row.getCell(cellNums[6]).setCellValue(gt300Qty);
		}
		if (gt300Amt != null) {
			row.getCell(cellNums[7]).setCellValue(gt300Amt);
		}

	}

	public String generateQuarterAddReport(int year, int quarter) {
		FileInputStream fis;
		try {
			PropKit.use("appconfig.properties", "UTF-8");
			String reportFolder = PropKit.get("reportFolder");
			fis = new FileInputStream(PathKit.getWebRootPath() + "/WEB-INF/example/季报.xls");
			HSSFWorkbook wb = new HSSFWorkbook(fis);
			produceAddBankTable(wb, year, quarter);// 生成分行别统计表
			produceAddAreaTable(wb, year, quarter);// 生成分辖区统计表
			String fileName = "季报累计" + year + "-第" + quarter + "季度.xls";
			FileOutputStream fos = new FileOutputStream(new File(reportFolder + "/" + fileName));
			HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
			wb.write(fos);
			fis.close();
			fos.close();
			return fileName;
		} catch (FileNotFoundException e) {
			LOG.error(e.getMessage(), e);
		} catch (IOException e) {
			LOG.error(e.getMessage(), e);
		}
		return null;
	}

	private void produceAddAreaTable(HSSFWorkbook wb, int year, int quarter) {
		HSSFSheet sheet = wb.getSheetAt(1);
		String[] areas = new String[] { "本级", "嘉善", "桐乡", "平湖", "海宁", "海盐" };
		for (int i = 0; i < areas.length; i++) {
			String area = areas[i];
			Row row = sheet.getRow(i + 8);
			Record branchRecord = quarterReportDao.sumAddBranchDataByArea(area, year, quarter);

			Integer branchQty = branchRecord.getInt("branch_qty");// 对公网点数量
			Integer edraftBranchQty = branchRecord.getInt("edraft_branch_qty");// 已加入电子商业汇票系统网点数量
			if (branchQty != null) {
				row.getCell(1).setCellValue(branchQty);
			}
			if (edraftBranchQty != null) {
				row.getCell(2).setCellValue(edraftBranchQty);
			}

			// 查询客户情况
			Record clientRecord = quarterReportDao.sumAddClientDataByArea(area, year, quarter);

			Integer euserQty = clientRecord.getInt("euser_qty");// 全部企业类客户数量
			if (euserQty != null) {
				row.getCell(3).setCellValue(euserQty);
			}
			Integer ebankEuserQty = clientRecord.getInt("ebank_euser_qty");// 开通网银的企业类客户数量
			if (ebankEuserQty != null) {
				row.getCell(4).setCellValue(ebankEuserQty);
			}

			Integer draftEuserQty = clientRecord.getInt("draft_euser_qty");// 已使用商业汇票客户数量
			if (draftEuserQty != null) {
				row.getCell(5).setCellValue(draftEuserQty);
			}
			Integer edraftEuserQty = clientRecord.getInt("edraft_euser_qty");// 已开通电子商业汇票业务客户数量

			if (edraftEuserQty != null) {
				row.getCell(6).setCellValue(edraftEuserQty);
			}

			// 查询业务量
			// 电子银行承兑汇票承兑
			setCellAddValueByArea(row, area, year, quarter, Constant.baEdraftAccept,
					new int[] { 7, 8, 9, 10, 11, 12, 13, 14 });
			// 电子银行承兑汇票贴现
			setCellAddValueByArea(row, area, year, quarter, Constant.baEdraftDiscount,
					new int[] { 15, 16, 17, 18, 19, 20, 21, 22 });
			// 纸质银行承兑汇票承兑
			setCellAddValueByArea(row, area, year, quarter, Constant.baDraftAccept,
					new int[] { 23, 24, 25, 26, 27, 28, 29, 30 });
			// 纸质银行承兑汇票贴现
			setCellAddValueByArea(row, area, year, quarter, Constant.baDraftDiscount,
					new int[] { 31, 32, 33, 34, 35, 36, 37, 38 });
			// 电子商业承兑汇票贴现
			setCellAddValueByArea(row, area, year, quarter, Constant.caEdraftDiscount,
					new int[] { 39, 40, 41, 42, 43, 44, 45, 46 });
			// 纸质商业承兑汇票贴现
			setCellAddValueByArea(row, area, year, quarter, Constant.caDraftDiscount,
					new int[] { 47, 48, 49, 50, 51, 52, 53, 54 });
			// 商业汇票再贴现
			setCellAddValueByArea(row, area, year, quarter, Constant.draftRediscount,
					new int[] { 55, 56, 57, 58, 59, 60, 61, 62 });
		}
	}

	private void produceAddBankTable(HSSFWorkbook wb, int year, int quarter) {
		HSSFSheet sheet = wb.getSheetAt(0);
		List<Record> banks = dao.findBanks();

		for (Record bankRecord : banks) {
			String bank = bankRecord.getStr("organization");
			int rowNum = bankRecord.getInt("row_num_sum");
			Row row = sheet.getRow(rowNum - 1);
			// 查询银行网点信息
			Record branchRecord = quarterReportDao.sumAddBranchDataByBank(bank, year, quarter);

			Integer branchQty = branchRecord.getInt("branch_qty");// 对公网点数量
			Integer edraftBranchQty = branchRecord.getInt("edraft_branch_qty");// 已加入电子商业汇票系统网点数量
			if (branchQty != null) {
				row.getCell(1).setCellValue(branchQty);
			}
			if (edraftBranchQty != null) {
				row.getCell(2).setCellValue(edraftBranchQty);
			}

			// 查询客户情况
			Record clientRecord = quarterReportDao.sumAddClientDataByBank(bank, year, quarter);

			Integer euserQty = clientRecord.getInt("euser_qty");// 全部企业类客户数量
			if (euserQty != null) {
				row.getCell(3).setCellValue(euserQty);
			}
			Integer ebankEuserQty = clientRecord.getInt("ebank_euser_qty");// 开通网银的企业类客户数量
			if (ebankEuserQty != null) {
				row.getCell(4).setCellValue(ebankEuserQty);
			}

			Integer draftEuserQty = clientRecord.getInt("draft_euser_qty");// 已使用商业汇票客户数量
			if (draftEuserQty != null) {
				row.getCell(5).setCellValue(draftEuserQty);
			}
			Integer edraftEuserQty = clientRecord.getInt("edraft_euser_qty");// 已开通电子商业汇票业务客户数量

			if (edraftEuserQty != null) {
				row.getCell(6).setCellValue(edraftEuserQty);
			}

			// 查询业务量
			// 电子银行承兑汇票承兑
			setCellAddValue(row, bank, year, quarter, Constant.baEdraftAccept
					, new int[] { 7, 8, 9, 10, 11, 12, 13, 14 });
			// 电子银行承兑汇票贴现
			setCellAddValue(row, bank, year, quarter, Constant.baEdraftDiscount,
					new int[] { 15, 16, 17, 18, 19, 20, 21, 22 });
			// 纸质银行承兑汇票承兑
			setCellAddValue(row, bank, year, quarter, Constant.baDraftAccept,
					new int[] { 23, 24, 25, 26, 27, 28, 29, 30 });
			// 纸质银行承兑汇票贴现
			setCellAddValue(row, bank, year, quarter, Constant.baDraftDiscount,
					new int[] { 31, 32, 33, 34, 35, 36, 37, 38 });
			// 电子商业承兑汇票贴现
			setCellAddValue(row, bank, year, quarter, Constant.caEdraftDiscount,
					new int[] { 39, 40, 41, 42, 43, 44, 45, 46 });
			// 纸质商业承兑汇票贴现
			setCellAddValue(row, bank, year, quarter, Constant.caDraftDiscount,
					new int[] { 47, 48, 49, 50, 51, 52, 53, 54 });
			// 商业汇票再贴现
			setCellAddValue(row, bank, year, quarter, Constant.draftRediscount,
					new int[] { 55, 56, 57, 58, 59, 60, 61, 62 });
		}
	}

	

	private void setCellAddValue(Row row, String bank, int year, int quarter, String billType, int[] cellNums) {
		// 50万以下
		Record less50record = quarterReportDao.sumAddBusinessDataByBank(bank, billType, Constant.less50, year, quarter);
		Integer less50Qty = less50record.getInt("qty");
		Double less50Amt = less50record.getDouble("amt");
		if (less50Qty != null) {
			row.getCell(cellNums[0]).setCellValue(less50Qty);
		}
		if (less50Amt != null) {
			row.getCell(cellNums[1]).setCellValue(less50Amt);
		}

		// 50-100万
		Record bet50To100Record = quarterReportDao.sumAddBusinessDataByBank(bank, billType, Constant.bet50To100, year,
				quarter);

		Integer bet50To100Qty = bet50To100Record.getInt("qty");
		Double bet50To100Amt = bet50To100Record.getDouble("amt");
		if (bet50To100Qty != null) {
			row.getCell(cellNums[2]).setCellValue(bet50To100Qty);
		}
		if (bet50To100Amt != null) {
			row.getCell(cellNums[3]).setCellValue(bet50To100Amt);
		}

		// 100-300万
		Record bet100To300Record = quarterReportDao.sumAddBusinessDataByBank(bank, billType, Constant.bet100To300, year,
				quarter);

		Integer bet100To300Qty = bet100To300Record.getInt("qty");
		Double bet100To300Amt = bet100To300Record.getDouble("amt");
		if (bet100To300Qty != null) {
			row.getCell(cellNums[4]).setCellValue(bet100To300Qty);
		}
		if (bet100To300Amt != null) {
			row.getCell(cellNums[5]).setCellValue(bet100To300Amt);
		}

		// 300万以上
		Record gt300Record = quarterReportDao.sumAddBusinessDataByBank(bank, billType, Constant.gt300, year, quarter);

		Integer gt300Qty = gt300Record.getInt("qty");
		Double gt300Amt = gt300Record.getDouble("amt");
		if (gt300Qty != null) {
			row.getCell(cellNums[6]).setCellValue(gt300Qty);
		}
		if (gt300Amt != null) {
			row.getCell(cellNums[7]).setCellValue(gt300Amt);
		}

	}

	private void setCellAddValueByArea(Row row, String area, int year, int quarter, String billType, int[] cellNums) {
		// 50万以下
		Record less50record = quarterReportDao.sumAddBusinessDataByArea(area
				, billType, Constant.less50, year, quarter);
		Integer less50Qty = less50record.getInt("qty");
		Double less50Amt = less50record.getDouble("amt");
		if (less50Qty != null) {
			row.getCell(cellNums[0]).setCellValue(less50Qty);
		}
		if (less50Amt != null) {
			row.getCell(cellNums[1]).setCellValue(less50Amt);
		}

		// 50-100万
		Record bet50To100Record = quarterReportDao.sumAddBusinessDataByArea(area
				, billType, Constant.bet50To100, year,
				quarter);

		Integer bet50To100Qty = bet50To100Record.getInt("qty");
		Double bet50To100Amt = bet50To100Record.getDouble("amt");
		if (bet50To100Qty != null) {
			row.getCell(cellNums[2]).setCellValue(bet50To100Qty);
		}
		if (bet50To100Amt != null) {
			row.getCell(cellNums[3]).setCellValue(bet50To100Amt);
		}

		// 100-300万
		Record bet100To300Record = quarterReportDao.sumAddBusinessDataByArea(area
				, billType, Constant.bet100To300, year,
				quarter);

		Integer bet100To300Qty = bet100To300Record.getInt("qty");
		Double bet100To300Amt = bet100To300Record.getDouble("amt");
		if (bet100To300Qty != null) {
			row.getCell(cellNums[4]).setCellValue(bet100To300Qty);
		}
		if (bet100To300Amt != null) {
			row.getCell(cellNums[5]).setCellValue(bet100To300Amt);
		}

		// 300万以上
		Record gt300Record = quarterReportDao.sumAddBusinessDataByArea(
				area, billType, Constant.gt300, year, quarter);

		Integer gt300Qty = gt300Record.getInt("qty");
		Double gt300Amt = gt300Record.getDouble("amt");
		if (gt300Qty != null) {
			row.getCell(cellNums[6]).setCellValue(gt300Qty);
		}
		if (gt300Amt != null) {
			row.getCell(cellNums[7]).setCellValue(gt300Amt);
		}

	}

}
